package com.linkstec.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.CharUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class DbUtil {
	private static final Logger logger = LogManager.getLogger();

	/**
	 * 表在数据库中是否存在
	 * @param tableName
	 * @return
	 */
	public static boolean checkTable(Connection conn,String tableName) {
		
//		Connection conn = BatchContext.getConnection(); 
		String chkTableName = tableName.toUpperCase();
		try {
			ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), null, chkTableName, new String[] { "TABLE" });  
			while (rs.next()) { 
				// 注意：结果集中存在表的很多信息，表名称在第 3 列  
				System.out.println("table = " + rs.getString(3));  
				if(tableName.equalsIgnoreCase( rs.getString(3))){
					return true;
				}
			}  
		} catch (SQLException e) {
			logger.error("", e);
		}
		return false;

//		throw new BusinessException("IASYS0001",tableName); 
	}
	
	
	/**
	 * 表字段存在Check
	 * @param tableName
	 * @return
	 */
	public static boolean checkColumn(Connection conn,String tableName,String columnName) {
//		Connection conn = BatchContext.getConnection(); 
		String chkTableName = tableName.toUpperCase();
		String chkColumnName = columnName.toUpperCase();
		
		try {
			
			ResultSet rs =conn.getMetaData().getColumns(conn.getCatalog(), null, chkTableName, chkColumnName);
			while (rs.next()) { 
				return true;
			}  
			
		} catch (SQLException e) {
			logger.error("", e);
			
		}
		return false;

	}
	
	/**
	 * 获取表的主键名字
	 * @param tableName
	 * @return
	 */
	public static String getPkName(Connection conn, String tableName) {
//		Connection conn = BatchContext.getConnection(); 
		try {
			
			ResultSet rs =conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, tableName);
			while (rs.next()) { 
				
				System.out.println("PrimaryKey = " + rs.getObject(6));  
			    return rs.getString(6);
			}  
			
		} catch (SQLException e) {
			logger.error("", e);
		}

		return null;
	}
	

	/**
	 * JDBC 数据更新插入操作
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int executeUpdate(Connection conn, String sql,
			List<Object> params) throws SQLException {
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if (params != null) {
			for (int i = 1; i <= params.size(); i++) {
				pstmt.setObject(i, params.get(i - 1));
			}
		}
		int result = -1;
		result = pstmt.executeUpdate();
		pstmt.close();
		pstmt = null;
		return result;
	}
	
	
	/**
	 * JDBC 查询数据
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 */
	public static Map<String, Object> queryOne(Connection conn, String sql,
			List<Object> params) throws SQLException {
		List<Map<String, Object>> list = queryList(conn, sql, params);
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	/**
	 * JDBC 查询数据
	 * @param conn
	 * @param sql
	 * @param params
	 * @return 
	 * @throws SQLException 
	 */
	public static List<Map<String, Object>> queryList(Connection conn, String sql,
			List<Object> params) throws SQLException {

		PreparedStatement pstmt = conn.prepareStatement(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		if (params != null) {
			for (int i = 1; i <= params.size(); i++) {
				pstmt.setObject(i, params.get(i - 1));
			}
		}

		ResultSet rs = pstmt.executeQuery();

		//获取字段信息
		String colName[] = getColumns(rs.getMetaData());
		
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		rs.beforeFirst();
		while (rs.next()) {
			Map<String, Object> item = new HashMap<String, Object>();
			for (int i = 0; i < colName.length; i++) {
				item.put(colName[i], rs.getObject(i + 1));
			}
			list.add(item);
		}
		rs.close();
		pstmt.close();
		return list;
	}

	/**
	 * 获取查询结果集的字段信息
	 * @param rsm
	 * @return
	 * @throws SQLException
	 */
	public static  String[] getColumns(ResultSetMetaData rsm) throws SQLException{
		int col = rsm.getColumnCount();  
		String colName[] = new String[col];
		for (int i = 0; i < col; i++) {   
			colName[i] = fieldToProperty(rsm.getColumnName( i + 1 ), false); 
		}
		return  colName;
	}
	
	/**
	 * 字段转换成对象属性 例如：user_name to userName
	 * @param field
	 * @param upcaseFirstChar
	 * @return
	 */
	public static String fieldToProperty(String field,boolean upcaseFirstChar) {
		if (null == field) {
			return "";
		}
		char[] chars = field.toLowerCase().toCharArray();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < chars.length; i++) {
			char c = chars[i];
			if (c == '_') {
				int j = i + 1;
				if (j < chars.length) {
					sb.append(StringUtils.upperCase(CharUtils.toString(chars[j])));
					i++;
				}
			} else {
				if(upcaseFirstChar && i == 0){
					sb.append(StringUtils.upperCase(CharUtils.toString(c)));
				}else{
					sb.append(c);
				}

			}
		}
		return sb.toString();
	}
	
	public static void closeConnection(Connection conn) throws SQLException{
		if(conn != null){
			conn.close();
			//去除链接状态监控
//			DbMonitorUtil.getInstance().remove(conn);
			conn = null;
		}
	}
	public static void main(String[] args) {
//		String driverClass = "oracle.jdbc.driver.OracleDriver";
//		String user = "mot_config_uat";
//        String password = "mot_config_uat";
//        String url = "jdbc:oracle:thin:@192.168.10.199:1521:orcl";
//        Connection conn = null;
//		try {
//		      Class.forName(driverClass);
//		      conn = DriverManager.getConnection(url, user, password);
////		      ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, "MOT_JOB");
//		      ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), "MOT_CONFIG_UAT", "MOT_JOB");
//		      ResultSetMetaData rsm = rs.getMetaData();
//		      int col = rsm.getColumnCount();  
//				String colName[] = new String[col];
//				for (int i = 0; i < col; i++) {   
//					colName[i] = rsm.getColumnName( i + 1 ); 
//				}
//				for (String ss : colName) {
//					logger.info(ss);
//				}
//				logger.info("+++++++++++++++++++++++++++++++++++++++++++++");
//				while (rs.next()) {
//					for (int i = 0; i < colName.length; i++) {
//						logger.info(colName[i] + "内容："+ rs.getObject(i + 1));
//					}
//					logger.info("+++++++++++++++++++++++++++++++++++++++++++++");
//				}
//		    } catch (ClassNotFoundException | SQLException e) {
//		    	logger.error("加载JDBC驱动失败(driver=" + driverClass + ")");
//		    }
//		testAddBatchOracle();
		testAddBatchMysql();
//		getPkName(conn, "MOT_JOB");
	}
	public static void testORA01461() {
		String driverClass = "oracle.jdbc.driver.OracleDriver";
		String user = "atom_config_dev";
		String password = "atom_config_dev";
		String url = "jdbc:oracle:thin:@192.168.10.199:1521:orcl";
		Connection conn = null;
		try {
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, user, password);
			
			List<Object> listData = new ArrayList<Object>();
			listData.add(123455);
			listData.add("CHANNEL");
			listData.add("CHANNEL_NAME");
			listData.add("RECEIVE_NO");
			listData.add("NODE_ID");
			listData.add("NODE_NAME");
			listData.add(20210903);
			listData.add(new Timestamp(System.currentTimeMillis()));
			listData.add("好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好的啊啊啊");
			
			String insertDmaMsgSendRes = "INSERT INTO DMA_MSG_SEND_RES( "
					+ "NODE_FLOW_ID,"//
					+ "CHANNEL, "//
					+ "CHANNEL_NAME, "//
					+ " RECEIVE_NO, "//
					+ "NODE_ID, "//
					+ "NODE_NAME, "//
					+ "SERV_DAY, "//
					+ "SEND_TIME, "//
					+ "RESULT_DESC"//
					+ ") VALUES (?,?,?,?,?,?,?,?,?) ";
			
			executeUpdate(conn,insertDmaMsgSendRes,listData);
			
		} catch (ClassNotFoundException | SQLException e) {
			logger.error("", e);
		}
	}
	public static void testAddBatchOracle() {
		String driverClass = "oracle.jdbc.driver.OracleDriver";
		String user = "atom_config_dev";
		String password = "atom_config_dev";
		String url = "jdbc:oracle:thin:@192.168.10.199:1521:orcl";
		Connection conn = null;
		try {
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, user, password);
			
			List<Object> listData = new ArrayList<Object>();
			listData.add(123455);
			listData.add("CHANNEL");
			listData.add("CHANNEL_NAME");
			listData.add("RECEIVE_NO");
			listData.add("NODE_ID");
			listData.add("NODE_NAME");
			listData.add(20210903);
			listData.add(new Timestamp(System.currentTimeMillis()));
			listData.add("好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好");
			
			String insertDmaMsgSendRes = "INSERT INTO DMA_MSG_SEND_RES( "
					+ "NODE_FLOW_ID,"//
					+ "CHANNEL, "//
					+ "CHANNEL_NAME, "//
					+ " RECEIVE_NO, "//
					+ "NODE_ID, "//
					+ "NODE_NAME, "//
					+ "SERV_DAY, "//
					+ "SEND_TIME, "//
					+ "RESULT_DESC"//
					+ ") VALUES (?,?,?,?,?,?,?,?,?) ";
			
			PreparedStatement pstmt = conn.prepareStatement(insertDmaMsgSendRes);
			if (listData != null) {
				for (int i = 1; i <= listData.size(); i++) {
					pstmt.setObject(i, listData.get(i - 1));
				}
			}
			long start = System.currentTimeMillis();
			for (int i =0;i< 1000000;i++) {
				pstmt.setObject(1, i);
				pstmt.addBatch();
				if (i%1000 ==0) {
					pstmt.executeBatch();
					logger.info(i + "入库");
				}
			}
			pstmt.executeBatch();
			pstmt.close();
			pstmt = null;
			long end = System.currentTimeMillis();
			logger.info("耗时" + (end - start)/1000 + "秒");
			
		} catch (ClassNotFoundException | SQLException e) {
			logger.error("", e);
		}
	}
	public static void testAddBatchMysql() {
		String driverClass = "com.mysql.cj.jdbc.Driver";
		String user = "root";
//		String password = "linkage@12345";
//		String url = "jdbc:mysql://192.168.2.234:3306/ec_advisor?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true";
		String url = "jdbc:mysql://localhost:3306/ec_advisor?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true";
		String password = "mysql";
		Connection conn = null;
		try {
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, user, password);
			
			List<Object> listData = new ArrayList<Object>();
			listData.add(123455);
			listData.add("CHANNEL");
			listData.add("CHANNEL_NAME");
			listData.add("RECEIVE_NO");
			listData.add("NODE_ID");
			listData.add("NODE_NAME");
			listData.add(20210903);
			listData.add(new Timestamp(System.currentTimeMillis()));
			listData.add("好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好好");
			
			String insertDmaMsgSendRes = "INSERT INTO DMA_MSG_SEND_RES( "
					+ "NODE_FLOW_ID,"//
					+ "CHANNEL, "//
					+ "CHANNEL_NAME, "//
					+ " RECEIVE_NO, "//
					+ "NODE_ID, "//
					+ "NODE_NAME, "//
					+ "SERV_DAY, "//
					+ "SEND_TIME, "//
					+ "RESULT_DESC"//
					+ ") VALUES (?,?,?,?,?,?,?,?,?) ";
			
			PreparedStatement pstmt = conn.prepareStatement(insertDmaMsgSendRes);
			if (listData != null) {
				for (int i = 1; i <= listData.size(); i++) {
					pstmt.setObject(i, listData.get(i - 1));
				}
			}
			long start = System.currentTimeMillis();
			for (int i =0;i< 1000000;i++) {
				pstmt.setObject(1, i);
				pstmt.addBatch();
				if (i%1000 ==0) {
					pstmt.executeBatch();
					logger.info(i + "入库");
				}
			}
			pstmt.executeBatch();
			pstmt.close();
			pstmt = null;
			long end = System.currentTimeMillis();
			logger.info("耗时" + (end - start)/1000/60 + "分" + (end - start)/1000%60 + "秒");
			
		} catch (ClassNotFoundException | SQLException e) {
			logger.error("", e);
		}
	}
}